#!/usr/bin/env python3
"""
数据清理工具：清理孤立的答案记录
用于解决答案记录与测评记录不匹配的问题
"""

import sys
import os
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from app import create_app, db
from app.models import Assessment, ScaleQuestion, AssessmentAnswer
from sqlalchemy import text

def find_orphaned_answers():
    """查找孤立的答案记录"""
    app = create_app()
    
    with app.app_context():
        print("=== 查找孤立的答案记录 ===")
        
        # 查找所有答案记录
        answers = db.session.execute(text("""
            SELECT aa.id, aa.assessment_id, aa.question_id, aa.answer_value,
                   a.scale_id as assessment_scale_id,
                   q.scale_id as question_scale_id
            FROM assessment_answers aa
            LEFT JOIN assessments a ON aa.assessment_id = a.id
            LEFT JOIN scale_questions q ON aa.question_id = q.id
            WHERE a.scale_id != q.scale_id OR a.id IS NULL OR q.id IS NULL
        """)).fetchall()
        
        if not answers:
            print("✅ 没有发现孤立的答案记录")
            return []
        
        print(f"🔍 发现 {len(answers)} 个孤立的答案记录：")
        for answer in answers:
            print(f"  答案ID: {answer.id}, 测评ID: {answer.assessment_id}, 题目ID: {answer.question_id}")
            print(f"    测评量表ID: {answer.assessment_scale_id}, 题目量表ID: {answer.question_scale_id}")
            print(f"    答案值: {answer.answer_value}")
            print()
        
        return answers

def cleanup_orphaned_answers(confirm=False):
    """清理孤立的答案记录"""
    app = create_app()
    
    with app.app_context():
        orphaned_answers = find_orphaned_answers()
        
        if not orphaned_answers:
            return
        
        if not confirm:
            print("⚠️  要删除这些孤立的答案记录，请运行：")
            print("python cleanup_orphaned_answers.py --confirm")
            return
        
        print("🗑️  开始删除孤立的答案记录...")
        
        for answer in orphaned_answers:
            db.session.execute(text("DELETE FROM assessment_answers WHERE id = :id"), 
                             {"id": answer.id})
            print(f"✅ 删除答案记录 ID: {answer.id}")
        
        db.session.commit()
        print(f"🎉 成功删除 {len(orphaned_answers)} 个孤立的答案记录")

def check_scale_6_status():
    """检查量表6的状态"""
    app = create_app()
    
    with app.app_context():
        print("=== 检查量表6的状态 ===")
        
        # 检查量表6的题目
        questions = db.session.execute(text("""
            SELECT id, question_text FROM scale_questions WHERE scale_id = 6
        """)).fetchall()
        
        print(f"量表6的题目数量: {len(questions)}")
        for q in questions:
            print(f"  题目ID: {q.id}, 内容: {q.question_text}")
        
        # 检查量表6题目的答案记录
        answers = db.session.execute(text("""
            SELECT aa.id, aa.assessment_id, aa.question_id, aa.answer_value
            FROM assessment_answers aa
            JOIN scale_questions q ON aa.question_id = q.id
            WHERE q.scale_id = 6
        """)).fetchall()
        
        print(f"\n量表6题目的答案记录数量: {len(answers)}")
        for answer in answers:
            print(f"  答案ID: {answer.id}, 测评ID: {answer.assessment_id}, 题目ID: {answer.question_id}, 答案值: {answer.answer_value}")
        
        # 检查这些答案记录对应的测评
        if answers:
            assessment_ids = list(set([a.assessment_id for a in answers]))
            assessments = db.session.execute(text("""
                SELECT id, scale_id, status FROM assessments WHERE id IN :ids
            """), {"ids": tuple(assessment_ids)}).fetchall()
            
            print(f"\n相关测评记录:")
            for assessment in assessments:
                print(f"  测评ID: {assessment.id}, 量表ID: {assessment.scale_id}, 状态: {assessment.status}")

if __name__ == "__main__":
    if len(sys.argv) > 1 and sys.argv[1] == "--confirm":
        cleanup_orphaned_answers(confirm=True)
    elif len(sys.argv) > 1 and sys.argv[1] == "--check":
        check_scale_6_status()
    else:
        print("数据清理工具")
        print("用法:")
        print("  python cleanup_orphaned_answers.py           # 查找孤立的答案记录")
        print("  python cleanup_orphaned_answers.py --check   # 检查量表6的状态")
        print("  python cleanup_orphaned_answers.py --confirm # 确认删除孤立的答案记录")
        print()
        find_orphaned_answers()